import csv
import pandas as pd
from tqdm import tqdm
import numpy as np
import os
from sklearn import preprocessing


def read_data(file_path):
    # file_path = "G:\\粤港澳数模\\附件：相关股票数据\\000028.SZ.xlsx"
    df_file_data = pd.read_excel(file_path)
    return df_file_data


def cal_num_day(df_file_data, stook_name):
    df_day_num = df_file_data[['时间', '成交量（手）']]
    # print(day_num)
    num_sum = 0
    day_sum = 0
    num_list = []
    day_start = str(df_day_num.iloc[0, 0])[0:8]
    day_end = ''
    temp = 0
    for index, row in tqdm(df_day_num.iterrows()):
        day_end = str(row['时间'])[0:8]
        if day_end == day_start:
            temp = int(row['成交量（手）'])
            continue
        else:
            day_start = day_end
            num_sum += temp
            day_sum += 1
            num_list.append(temp)
            # print(num_sum,day_sum)
    num_sum += int(df_day_num.tail(n=1)['成交量（手）'])
    day_sum += 1
    num_list.append(int(df_day_num.tail(n=1)['成交量（手）']))
    print(stook_name,
          '每日交易量（手）平均值{}，方差为{}，标准差为{}\n'.format(np.mean(num_list), np.var(num_list), np.std(num_list)))
    return np.mean(num_list), np.var(num_list), np.std(num_list), num_list


def cal_sum_day(df_file_data, stook_name):
    df_day_sum = df_file_data[['时间', '成交额（元）']]
    # print(day_sum)
    sum_sum = 0.0
    day_sum = 0
    sum_list = []
    day_start = str(df_day_sum.iloc[0, 0])[0:8]
    day_end = ''
    temp = 0.0
    for index, row in tqdm(df_day_sum.iterrows()):
        day_end = str(row['时间'])[0:8]
        if day_end == day_start:
            temp = float(row['成交额（元）'])
            continue
        else:
            day_start = day_end
            sum_sum += temp
            day_sum += 1
            # print(sum_sum,day_sum)
            sum_list.append(temp)
    sum_sum += float(df_day_sum.tail(n=1)['成交额（元）'])
    day_sum += 1
    sum_list.append(float(df_day_sum.tail(n=1)['成交额（元）']))
    # print(stook_name, '平均日交易额（元）：', sum_sum/day_sum, '\n')
    print(stook_name,
          '每日交易额（元）平均值{}，方差为{}，标准差为{}\n'.format(np.mean(sum_list), np.var(sum_list), np.std(sum_list)))
    return np.mean(sum_list), np.var(sum_list), np.std(sum_list), sum_list


def cal_high_low_distance_permin(df_file_data, stook_name):
    df_high_low_distance = df_file_data[['时间', '收盘价', '最高价', '最低价']]
    distance_dic = {}
    distance_list = []
    for index, row in tqdm(df_high_low_distance.iterrows()):
        min_end = str(row['时间'])[4:12]
        if int(min_end[4:8]) >= 925:
            distance_dic[min_end] = float((row['最高价'] - row['最低价']) * 100 / row['收盘价'])  # 算的是百分比
    for i in distance_dic:
        distance_list.append(distance_dic[i])
    # print(len(distance_list))
    avg_high_low_distance_permin = np.mean(distance_list)
    var_high_low_distance_permin = np.var(distance_list)
    std_high_low_distance_permin = np.std(distance_list, ddof=1)
    print(stook_name, '以一分钟为间隔的最高价和最低价之间振幅的平均值为{}，方差为{}，标准差为{}\n'.format(
        avg_high_low_distance_permin, var_high_low_distance_permin, std_high_low_distance_permin))
    return avg_high_low_distance_permin, var_high_low_distance_permin, std_high_low_distance_permin, distance_list

def box_plot_data(stock_name, distance_list):
    if stock_name == '德赛电池000049.SZ' or stock_name == '美盈森002303.SZ':
        label_list = ['low' for _ in range(len(distance_list))]
    elif stock_name == '珠江啤酒002461.SZ' or stock_name == '白云山600332.SH':
        label_list = ['mid' for _ in range(len(distance_list))]
    elif stock_name == '格力电器000651.SZ' or stock_name == '中国平安601318.SH':
        label_list = ['high' for _ in range(len(distance_list))]
    name_list = [stock_name for _ in range(len(distance_list))]
    return list(zip(distance_list, name_list, label_list))

def box_plot_data_2(distance_zip_list):
    list_1 = []
    list_2 = []
    list_3 = []
    for i in distance_zip_list:
        list_1.append(i[0])
        list_2.append(i[1])
        list_3.append(i[2])
    return list_1, list_2, list_3

if __name__ == "__main__":
    stock_dic = {'002027': '分众传媒', '000069': '华侨城Ａ', '002233': '塔牌集团', '300014': '亿纬锂能',
                 '600383': '金地集团', '002060': '粤水电',
                 '002475': '立讯精密', '600048': '保利地产', '002352': '顺丰控股', '000636': '风华高科',
                 '001914': '招商积余', '002511': '中顺洁柔',
                 '002449': '国星光电', '601318': '中国平安', '002303': '美盈森', '600183': '生益科技',
                 '600323': '瀚蓝环境', '002461': '珠江啤酒',
                 '000049': '德赛电池', '002152': '广电运通', '600872': '中炬高新', '002138': '顺络电子',
                 '000921': '海信家电', '600332': '白云山',
                 '300115': '长盈精密', '002035': '华帝股份', '000513': '丽珠集团', '600325': '华发股份',
                 '000651': '格力电器', '000028': '国药一致',
                 '601788': '光大证券', '000333': '美的集团', '002594': '比亚迪'
                 }

    stock_name_list = []

    num_list_list = []
    sum_list_list = []

    distance_zip_list = []

    file_name_list = ["000049.SZ.xlsx", '000651.SZ.xlsx', '002303.SZ.xlsx', '002461.SZ.xlsx', '600332.SH.xlsx',
                      '601318.SH.xlsx']
    # file_name_list = os.listdir('G:\\粤港澳数模\\附件：相关股票数据')
    print(len(file_name_list))
    for i in file_name_list:
        stock_name = stock_dic[i[0:6]] + i[0:9]
        print(stock_name)
        stock_name_list.append(stock_name)
        file_path = 'G:\\粤港澳数模\\附件：相关股票数据\\' + i
        df_file_data = read_data(file_path)

        # _1, _2, _3, num_list = cal_num_day(df_file_data, stock_name)
        # _4, _5, _6, sum_list = cal_sum_day(df_file_data, stock_name)
        _7, _8, _9, distance_list = cal_high_low_distance_permin(df_file_data, stock_name)
        print(len(distance_list))

        # num_list_list.append(num_list)
        # sum_list_list.append(sum_list)
        box_data = box_plot_data(stock_name, distance_list)
        # print(box_data)
        for each_zip in box_data:
            distance_zip_list.append(each_zip)
    print(len(distance_zip_list))
    list1, list2, list3 = box_plot_data_2(distance_zip_list)
    df_plot_data = pd.DataFrame({'value': list1, 'stock_name': list2, 'label': list3})
    print(df_plot_data)
    df_plot_data.to_csv("G:\\粤港澳数模\\plot_data.csv")

    # stock_num_perday = zip(stock_name_list, num_list_list)
    # stock_num_perday_df_dic = {}
    # for i in stock_num_perday:
    #     stock_num_perday_df_dic[i[0]] = i[1]
    # stock_num_perday_df = pd.DataFrame(stock_num_perday_df_dic)
    # print(stock_num_perday_df)
    # stock_num_perday_df.to_csv("G:\\粤港澳数模\\stock_num_perday.csv")
    #
    #
    # stock_sum_perday = zip(stock_name_list, sum_list_list)
    # stock_sum_perday_df_dic = {}
    # for i in stock_sum_perday:
    #     stock_sum_perday_df_dic[i[0]] = i[1]
    # stock_sum_perday_df = pd.DataFrame(stock_sum_perday_df_dic)
    # print(stock_sum_perday_df)
    # stock_sum_perday_df.to_csv("G:\\粤港澳数模\\stock_sum_perday.csv")

